#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
MySQL数据库管理器
统一管理MySQL连接和操作
"""

from typing import Optional, Dict, Any, List
from sqlalchemy import create_engine, text
from sqlalchemy.orm import sessionmaker, Session
from sqlalchemy.exc import SQLAlchemyError
import json
from datetime import datetime
from config import current_config
from models import db, User, Project, Interface, TestCase


class MySQLManager:
    """MySQL数据库管理器"""
    
    def __init__(self):
        self._engine = None
        self._session_factory = None
        self._mysql_uri: Optional[str] = None
        # 默认使用配置文件初始化
        self._initialize_connection()
    
    def init_app(self, app=None, mysql_uri: str = None):
        """初始化应用连接"""
        self._mysql_uri = mysql_uri or current_config.MYSQL_URI
        
        if app:
            # Flask应用集成
            app.config['SQLALCHEMY_DATABASE_URI'] = self._mysql_uri
            app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = current_config.SQLALCHEMY_TRACK_MODIFICATIONS
            app.config['SQLALCHEMY_ECHO'] = current_config.SQLALCHEMY_ECHO
            
            db.init_app(app)
            
            with app.app_context():
                self._create_tables()
        else:
            self._initialize_connection()
    
    def _initialize_connection(self):
        """初始化数据库连接"""
        try:
            # 使用配置的URI或默认配置
            uri = self._mysql_uri or current_config.MYSQL_URI
            
            self._engine = create_engine(
                uri,
                echo=current_config.SQLALCHEMY_ECHO,
                pool_recycle=3600,  # 1小时回收连接
                pool_pre_ping=True,  # 连接前测试
                connect_args={
                    'charset': 'utf8mb4',
                    'init_command': "SET sql_mode='STRICT_TRANS_TABLES'"
                }
            )
            
            self._session_factory = sessionmaker(bind=self._engine)
            
            # 测试连接
            with self._engine.connect() as conn:
                conn.execute(text("SELECT 1"))
            
            print(f"✅ MySQL连接成功: {current_config.MYSQL_DB}")
            
        except Exception as e:
            print(f"❌ MySQL连接失败: {e}")
            self._engine = None
            self._session_factory = None
    
    def _create_tables(self):
        """创建数据表"""
        try:
            if self._engine:
                db.metadata.create_all(self._engine)
                print("✅ 数据表创建完成")
            else:
                print("❌ 数据库引擎未初始化")
        except Exception as e:
            print(f"❌ 创建数据表失败: {e}")
    
    @property
    def engine(self):
        """获取数据库引擎"""
        return self._engine
    
    def get_session(self) -> Optional[Session]:
        """获取数据库会话"""
        if self._session_factory is None:
            return None
        return self._session_factory()
    
    def health_check(self) -> Dict[str, Any]:
        """
        数据库健康检查
        
        Returns:
            健康状态字典
        """
        try:
            if self._engine is None:
                return {
                    'status': 'error',
                    'message': 'Database not connected'
                }
            
            # 测试连接
            with self._engine.connect() as conn:
                result = conn.execute(text("SELECT VERSION()"))
                mysql_version = result.scalar()
                
                # 获取数据库统计信息
                result = conn.execute(text(f"SELECT COUNT(*) as table_count FROM information_schema.tables WHERE table_schema = '{current_config.MYSQL_DB}'"))
                table_count = result.scalar()
            
            return {
                'status': 'healthy',
                'message': 'Database connection is healthy',
                'details': {
                    'mysql_version': mysql_version,
                    'database_name': current_config.MYSQL_DB,
                    'table_count': table_count,
                    'engine_type': 'MySQL'
                }
            }
            
        except Exception as e:
            return {
                'status': 'error',
                'message': f'Database health check failed: {str(e)}'
            }
    
    def create_indexes(self):
        """创建必要的索引（通过模型定义自动创建）"""
        try:
            if self._engine is None:
                return
            
            # 索引已在模型中定义，这里可以添加额外的索引
            with self._engine.connect() as conn:
                # 用户表额外索引
                try:
                    conn.execute(text("CREATE INDEX idx_users_role ON users(role)"))
                except:
                    pass  # 索引可能已存在
                
                try:
                    conn.execute(text("CREATE INDEX idx_users_active ON users(is_active)"))
                except:
                    pass
                
                # 项目表额外索引
                try:
                    conn.execute(text("CREATE INDEX idx_projects_created_by_status ON projects(created_by, status)"))
                except:
                    pass
                
                # 接口表额外索引  
                try:
                    conn.execute(text("CREATE INDEX idx_interfaces_project_method ON interfaces(project_id, method)"))
                except:
                    pass
                
                # 测试案例表额外索引
                try:
                    conn.execute(text("CREATE INDEX idx_test_cases_type_status ON test_cases(type, status)"))
                except:
                    pass
                
                conn.commit()
                print("✅ 额外索引创建完成")
                
        except Exception as e:
            print(f"❌ 创建索引失败: {e}")
    
    def execute_raw_sql(self, sql: str, params: Dict = None) -> List[Dict]:
        """执行原始SQL查询"""
        try:
            with self._engine.connect() as conn:
                result = conn.execute(text(sql), params or {})
                
                if result.returns_rows:
                    columns = result.keys()
                    rows = result.fetchall()
                    return [dict(zip(columns, row)) for row in rows]
                else:
                    conn.commit()
                    return []
                    
        except Exception as e:
            print(f"❌ 执行SQL失败: {e}")
            return []
    
    def close_connection(self):
        """关闭数据库连接"""
        if self._engine:
            self._engine.dispose()
            print("✅ 数据库连接已关闭")
    
    def __enter__(self):
        """上下文管理器进入"""
        return self
    
    def __exit__(self, exc_type, exc_val, exc_tb):
        """上下文管理器退出"""
        self.close_connection()


# 全局MySQL管理器实例
mysql_manager = MySQLManager()